/**
 * Created by xxcao on 2017/6/5.
 */


var user = {
    insert:'insert into user(user_id, user_name, password) values(REPLACE(UUID(),\'-\',\'\'),?,?)',
    findOne:'select * from user where user_name = ? and password = ?',
    updateOne:'update user set ? = ? where ? = ?',
    findOneWC:'select * from user where binding_wechat_id = ?',
    insertWC:'insert into user(user_id, user_name, nick_name, binding_wechat_id, head_image_url) values(REPLACE(UUID(),\'-\',\'\'),?,?,?,?)',
    updateClientId:'update user set client_id = ? where user_id = ?',
    findClientIds:'select * from user where user_id in (?)',
    findUser:'select * from user where user_id = ?',
    findRelatedUser:'select distinct d.* from user2board as a left outer join board as b on (a.board_id = b.board_id) \
                     left outer join member as c on (b.board_id = c.board_id) left outer join user as d on (c.user_id = d.user_id) \
                     where a.user_id = ? and d.user_id <> ?',
    findRelatedUserPage:'select distinct d.* from user2board as a left outer join board as b on (a.board_id = b.board_id) \
                     left outer join member as c on (b.board_id = c.board_id) left outer join user as d on (c.user_id = d.user_id) \
                     where a.user_id = ? and d.user_id <> ? limit ? offset ?',
    findRelatedUserCount:'select count(*) as count from (select distinct d.* from user2board as a left outer join board as b on (a.board_id = b.board_id) \
                     left outer join member as c on (b.board_id = c.board_id) left outer join user as d on (c.user_id = d.user_id) \
                     where a.user_id = ? and d.user_id <> ?)',
};

var board = {
    insert:'insert into board(board_id, board_name, board_remark, board_logo, current_status, \
    phase_count, creator, create_date, last_edit_date) values(?,?,?,?,1,?,?,now(),now())',
    findOne:'select b.* from user2board as a left outer join board as b on (a.board_id = b.board_id) \
    where a.user_id = ? and b.board_name = ? and b.current_status = 1',
    findsPage:'select b.*, a.is_top, a.is_disturb, a.top_date, a.note_size, c.user_name from user2board as a left outer join board as b on (a.board_id = b.board_id) left outer join user as c on (a.user_id = c.user_id)\
    where a.user_id = ? and b.current_status = 1 order by a.is_top desc, a.top_date desc, b.create_date desc limit ? offset ?',
    findsAll:'select b.*, a.is_top, a.is_disturb, a.top_date, a.note_size, c.user_name from user2board as a left outer join board as b on (a.board_id = b.board_id) left outer join user as c on (a.user_id = c.user_id)\
    where a.user_id = ? and b.current_status = 1 order by a.is_top desc, a.top_date desc, b.create_date desc',
    count:'select count(*) as count from user2board as a left outer join board as b on (a.board_id = b.board_id) where a.user_id = ? and b.current_status = 1',
    insertPhase:'insert into phase(phase_id, board_id, phase_name, create_date, last_edit_date) values(REPLACE(UUID(),\'-\',\'\'), ?, ?,now(),now())',
    insertMember:'insert into member(user_id, board_id, is_master, create_date, last_edit_date) values(?, ?, ?, now(), now())',
    insertUser2Board:'insert into user2board(user_id, board_id, is_top, is_disturb, top_date, note_size) values(?, ?, 0, 0, null, 1)',
    findPersonInGroup:'select * from user2board as a left outer join board as b on (a.board_id = b.board_id) where a.user_id = ? and a.board_id = ? and b.current_status = 1',
    deletePersonInGroup:'delete from user2board where user_id = ? and board_id = ?',
    deleteGroupsPerson:'delete from member where user_id = ? and board_id = ?',
    findMaster:'select * from member where user_id = ? and board_id = ? and is_master = 1',
    deleteBoard:'update board set current_status = 2 where board_id = ?',
    updateIsTop:'update user2board set is_top = 1, top_date = now() where user_id = ? and board_id = ?',
    updateIsNotTop:'update user2board set is_top = 0, top_date = null where user_id = ? and board_id = ?',
    updateIsNotDisturb:'update user2board set is_disturb = 0 where user_id = ? and board_id = ?',
    updateIsDisturb:'update user2board set is_disturb = 1 where user_id and board_id = ?',
    findBoardDetail:'select * from board where board_id = ?',
    findBoardPhases:'select * from phase where board_id = ?',
    findBoardMembers:'select a.user_id, b.nick_name, b.head_image_url, a.is_master from member as a left outer join user as b \
    on(a.user_id = b.user_id) where a.board_id = ?',
    findBoardMembersAll:'select a.user_id, b.nick_name, b.head_image_url, a.is_master, b.client_id, b.user_name, c.board_name, c.board_id from member as a left outer join user as b \
    on (a.user_id = b.user_id) left outer join board as c on (a.board_id = c.board_id) where a.board_id = ?',
    findBoardAttributes:'select * from attribute where board_id = ?',
    updateNoteSize:'update user2board set note_size = ? where user_id = ? and board_id = ?',
    updateBoardName:'update board set board_name = ?, last_edit_date = now() where board_id = ?',
    updateBoardRemark:'update board set board_remark = ?, last_edit_date = now() where board_id = ?',
};

var task = {
    insert:'insert into task(task_id, board_id, task_content, task_remark, issue_count, \
    creator, task_executor, active_date, current_status, finish_date, current_phase_id, current_attribute_id, create_date, last_edit_date \
    ) values(?, ?, ?, ?, 0, ?, ?, ?, ?, ?, ?, ?, now(), now())',
    findOne:'select * from task where task_id = ?',
    finds:'select * from task where board_id = ? and (current_status = 1 or current_status = 2)',
    findsWithPhaseId:'select * from task where board_id = ? and current_phase_id = ? and (current_status = 1 or current_status = 2)',
    deleteOne:'update task set current_status = 4 where task_id = ?',
    updateWithoutExcutor:'update task set current_status = 1, last_edit_date = now(), task_executor = null, active_date = null where task_id = ? and current_phase_id = ?',
    updateWithExcutor:'update task set current_status = 2 ,last_edit_date = now(), task_executor = ?, active_date = now() where task_id = ? and current_phase_id = ?',
    findSameName:'select * from task where task_content = ? and board_id = ?',
    insertAttachment:'insert into attachment(item_id, attachment_id, attachment_type, attachment_url) values(?, REPLACE(UUID(),\'-\',\'\'), 0, ?)',
    updateCurrentPhase:'update task set current_phase_id = ?, last_edit_date = now() where task_id = ?',
    updateTask:'update task set ? = ?, last_edit_date = now() where task_id = ?',
    insertComment:'insert into comment(comment_id, item_id, comment_content, creation_date, is_has_attachment, creator) values(?, ?, ?, now(), ?, ?)',
    findsWithPhaseIdOrdered:'select * from task where board_id = ? and current_phase_id = ? and (current_status = 1 or current_status = 2) order by create_date',
    insertArchive:'insert into archive(archive_id, board_id, start_date, end_date, create_date, last_edit_date, task_count) values(?, ?, ?, ?, now(), now(), ?)',
    updateArchive:'update task set current_status = 3, archive_id = ?, current_phase_id = null, last_edit_date = now() where board_id = ? and current_phase_id = ?',
    findArchives:'select * from archive where board_id = ? order by create_date desc',
    findArchiveTasks:'select * from task where archive_id = ? and current_status = 3'
};

var issue = {
    insert:'insert into issue(issue_id, task_id, issue_content, issue_remark, creator, current_status, create_date, last_edit_date) values (?, ?, ?, ?, ?, 0, now(), now())',
    findOne:'select * from issue where task_id = ? and issue_content = ?',
    findIssue:'select * from issue where issue_id = ?',
    finds:'select * from issue where task_id = ?',
    deleteOne:'delete from issue where issue_id = ?',
    updateOne:'update task set issue_count = ?, last_edit_date = now() where task_id = ?',
    updateStatus:'update issue set current_status = ? where issue_id = ?'
};


var news = {
    insert:'insert into news(news_id, news_description, user_id, action_type, create_date, last_edit_date, board_id) \
    values(?, ?, ?, ?, now(), now(), ?)',
    findsPage:'select a.*, c.user_name from news as a left outer join user2board as b on (a.board_id = b.board_id) \
    left outer join user as c on (b.user_id = c.user_id) where b.user_id = ? order by a.create_date desc limit ? offset ?',
    findsWithBoardIdPage:'select a.*, c.user_name from news as a left outer join user2board as b on (a.board_id = b.board_id) \
    left outer join user as c on (b.user_id = c_user_id) where b.user_id = ? and a.board_id = ? order by a.create_date desc limit ? offset ?',
    finds:'select a.*, c.user_name from news as a left outer join user2board as b on (a.board_id = b.board_id) \
    left outer join user as c on (b.user_id = c.user_id) where b.user_id = ? order by a.create_date desc',
    findsWithBoardId:'select a.*, c.user_name from news as a left outer join user2board as b on (a.board_id = b.board_id) \
    left outer join user as c on (b.user_id = c_user_id) where b.user_id = ? and a.board_id = ? order by a.create_date desc',
    findsCount:'select count(*) as count from news as a left outer join user2board as b on (a.board_id = b.board_id) \
    left outer join user as c on (b.user_id = c.user_id) where b.user_id = ? order by a.create_date desc',
    findsWithBoardIdCount:'select count(*) as count from news as a left outer join user2board as b on (a.board_id = b.board_id) \
    left outer join user as c on (b.user_id = c_user_id) where b.user_id = ? and a.board_id = ? order by a.create_date desc',
};


module.exports = {
    user,
    board,
    task,
    issue,
    news
};